package com.artup.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import com.artup.pojo.Works;

/**
 * 作品
 * @author hapday
 * @date 2017年7月14日 @Time 下午6:37:48
 */
@Repository
public interface WorksDao {
	/**
	 * 添加【作品】
	 * @param works 作品
	 * @throws SQLException
	 */
	@Insert( { "INSERT INTO bud_edit(db_id, user_db_id, client, name, quantity, created_dt, finish_dt, channel_code, status, sku, thumbnail_image_url, category, size, shape, color, sku_code, price, thumbnail_id, tpl_code) "
			+ "VALUES (#{id, javaType=String, jdbcType=VARCHAR}, #{passportId, javaType=Integer, jdbcType=INTEGER}, #{clientCode, javaType=String, jdbcType=VARCHAR}"
			+ ", #{name, javaType=String, jdbcType=VARCHAR}, #{quantity, javaType=Integer, jdbcType=INTEGER}"
			+ ", NOW(), NOW(), #{channelCode, javaType=String, jdbcType=VARCHAR}, #{status, javaType=Byte, jdbcType=TINYINT}, #{sku, javaType=String, jdbcType=VARCHAR}"
			+ ", #{thumbnailPath, javaType=String, jdbcType=VARCHAR}, #{typeCode, javaType=String, jdbcType=VARCHAR}, #{size, javaType=String, jdbcType=VARCHAR}"
			+ ", #{shape, javaType=String, jdbcType=VARCHAR}, #{color, javaType=String, jdbcType=VARCHAR}, #{skuCode, javaType=String, jdbcType=VARCHAR}"
			+ ", #{price, javaType=Float, jdbcType=FLOAT}, #{thumbnailId, javaType=String, jdbcType=VARCHAR}, #{templateId, javaType=String, jdbcType=VARCHAR})" } )
	void insertWorks(Works works) throws SQLException;
	
	/**
	 * 根据【订单IDs】查询【作品列表】
	 * @param orderIds 【订单IDs】
	 * @return 【作品列表】
	 * @throws SQLException
	 */
	@Select( value = { "<script>"
			+ "SELECT "
			+ "DISTINCT w.db_id as id, w.user_db_id as passportid, w.name, w.price, w.thumbnail_image_url AS thumbnailPath, w.category AS typeCode, w.shape, w.color, w.size, owr2.order_db_id AS orderId, owr2.works_quantity AS quantity, w.sku_code AS skuCode"
			+ " FROM bud_edit w INNER JOIN bud_order_product_r owr2 ON owr2.car_db_id = w.db_id "
			+ " WHERE w.db_id IN ("
			+ "		SELECT owr.car_db_id"
			+ "		 FROM bud_order_product_r owr"
			+ "		 WHERE owr.order_db_id IN"
						+ "<foreach collection='orderIds' item='orderId' open='(' close=')' separator=','>"
							+ "#{orderId, javaType=String, jdbcType=VARCHAR}"
						+ "</foreach>"
			+ ")"
			+ "</script>" } )
	List<Works> selectWorksListByOrderIds(@Param( "orderIds" ) String [] orderIds) throws SQLException;

	/**
	 * 根据【订单ID】查询【作品列表】
	 * @param orderIds 【订单ID】
	 * @return 【作品列表】
	 * @throws SQLException
	 */
	@Select( value = { "<script>"
			+ "SELECT "
			+ "DISTINCT w.db_id as id, w.user_db_id as passportId, w.name, w.price, w.thumbnail_image_url AS thumbnailPath, w.category AS typeCode, w.shape, w.color, w.size, owr2.order_db_id AS orderId, owr2.works_quantity AS quantity"
			+ " FROM bud_edit w INNER JOIN bud_order_product_r owr2 ON owr2.car_db_id = w.db_id "
			+ "	WHERE owr2.order_db_id = #{orderId, javaType=String, jdbcType=VARCHAR}"
			/*+ " WHERE w.db_id IN ("
			+ "		SELECT owr.car_db_id"
			+ "		 FROM bud_order_product_r owr"
			+ "		 WHERE owr.order_db_id = #{orderId, javaType=String, jdbcType=VARCHAR}"
			+ ")"*/
			+ "</script>" } )
	List<Works> selectWorksListByOrderId(@Param( "orderId" ) String orderId) throws SQLException;
	
	/**
	 * 根据【订单号】查询【作品列表】
	 * @param orderCode 订单号
	 * @return 作品列表
	 * @throws SQLException
	 */
	@Select( value = { "SELECT w.db_id AS id, w.user_db_id AS passportId, w.client AS clientCode, w.thumbnail_image_url AS thumbnailPath, w.created_dt AS createTime, w.finish_dt AS finishTime, w.channel_code AS channelCode, w.status, w.category AS typeCode, w.sku_code AS skuCode, w.name, w.PDF_PATH AS pdfPath FROM bud_edit w INNER JOIN bud_order_product_r owr ON w.DB_ID = owr.car_db_id INNER JOIN bud_order o ON owr.order_db_id = o.db_id WHERE o.code = #{code, javaType=String, jdbcType=VARCHAR}" } )
	List<Works> selectWorksListByOrderCode(String orderCode) throws SQLException;
	
	/**
	 * 根据【ID】查询【作品】
	 * @param id 【ID】
	 * @return 【作品】
	 * @throws SQLException
	 */
	@Select( value = { "SELECT w.db_id AS id, w.user_db_id AS passportId, w.name, w.price, w.quantity, w.category AS typeCode, w.status, w.sku_code AS skuCode, w.shape, w.color, w.size, w.created_dt AS createTime, w.finish_dt AS finishTime, w.thumbnail_image_url AS thumbnailPath FROM bud_edit w WHERE w.DB_ID = #{id, javaType=String, jdbcType=VARCHAR}" } )
	Works selectWorksById(String id) throws SQLException;
	
//	void batchUpdateWorksRecycleById(String [] ids) throws SQLException;
	
	/**
	 * 根据【IDs】批量删除【作品】
	 * @param ids 【IDs】
	 * @throws SQLException
	 */
	@Delete( value = { "<script>"
				+ "DELETE FROM bud_edit WHERE db_id IN "
				+ "<foreach collection='ids' item='id' open='(' close=')' separator=','>"
					+ "#{id, javaType=String, jdbcType=VARCHAR}"
				+ "</foreach>"
			+ "</script>" } )
	void batchDeleteWorksByIds(@Param("ids") String [] ids) throws SQLException;
	
	/**
	 * 模糊查询【作品总记录数】
	 * @param works
	 * @return 【作品总记录数】
	 * @throws SQLException
	 */
	@Select( value = { "<script>SELECT COUNT(1)"
			+ " FROM bud_edit w"
			+ " WHERE w.is_recycle = 2"
				+ " AND (w.CLIENT = 'ios' OR w.CLIENT = 'android')"
				+ "<if test='null != passportId and 0 &lt; passportId'>"
					+ " AND w.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}"
				+ "</if>"
				+ "<if test='null != status and 0 &lt; status'>"
					+ " AND w.status = #{status, javaType=Byte, jdbcType=TINYINT}"
				+ "</if>"
				+ "<if test='null != name and \"\" != name'>"
					+ " AND w.name LIKE CONCAT('%', #{name}, '%')"
				+ "</if>"
				+ "<if test='null != typeCode and \"\" != typeCode'>"
					+ " AND w.category = #{typeCode, javaType=String, jdbcType=VARCHAR}"
				+ "</if>"
	      		+ "<if test='null != beginFinishTime and \"\" != beginFinishTime'>"
	      			+ " AND finish_dt >= #{beginFinishTime}"
	      		+ "</if>"
	      		+ "<if test='null != endFinishTime and \"\" != endFinishTime'>"
	      			+ " AND finish_dt &lt;= #{endFinishTime}"
	      		+ "</if>"
			+ "</script>" } )
	long selectWorksTotalCount(Works works) throws SQLException;

	/**
	 * 模糊查询【作品列表】
	 * @param works
	 * @return 【作品列表】
	 * @throws SQLException
	 */
	@Select( value = { "<script>"
			+ "SELECT w.db_id AS id, w.USER_DB_ID AS passportId, w.name, w.CLIENT AS clientCode, w.created_dt AS createTime, w.finish_dt AS finishTime, w.status, w.thumbnail_image_url AS thumbnailPath, w.category AS typeCode, w.sku_code AS skuCode, w.price, w.quantity, w.shape, w.shape, w.color "
			+ " FROM bud_edit w"
			+ " WHERE w.is_recycle = 2"
				+ " AND (w.CLIENT = 'ios' OR w.CLIENT = 'android')"
				+ "<if test='null != passportId and 0 &lt; passportId'>"
					+ " AND w.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}"
				+ "</if>"
				+ "<if test='null != status and 0 &lt; status'>"
					+ " AND w.status = #{status, javaType=Byte, jdbcType=TINYINT}"
				+ "</if>"
				+ "<if test='null != name and \"\" != name'>"
					+ " AND w.name LIKE CONCAT('%', #{name}, '%')"
				+ "</if>"
				+ "<if test='null != typeCode and \"\" != typeCode'>"
					+ " AND w.category = #{typeCode, javaType=String, jdbcType=VARCHAR}"
				+ "</if>"
				+ "<if test='null != beginFinishTime and \"\" != beginFinishTime and null != endFinishTime and \"\" != endFinishTime'>"
	      			+ " AND finish_dt >= #{beginFinishTime} AND finish_dt &lt;= #{endFinishTime}"
	      		+ "</if>"
			+ " ORDER BY CREATED_DT DESC"
			+ " LIMIT #{offset, javaType=Long, jdbcType=BIGINT}, #{pageSize, javaType=Integer, jdbcType=INTEGER}"
			+ "</script>" } )
	List<Works> selectWorksList(Works works) throws SQLException;
	
	/**
	 * 根据【ID】更新【PDF路径】
	 * @param id 【ID】
	 * @param pdfPath 【PDF路径】
	 * @throws SQLException
	 */
	@Update( value = { "UPDATE bud_edit w SET w.pdf_path = #{pdfPath, javaType=String, jdbcType=VARCHAR} WHERE w.db_id = #{id, javaType=String, jdbcType=VARCHAR}" } )
	void updateWorksPdfPathById(@Param("id") String id, @Param("pdfPath") String pdfPath) throws SQLException;
	
	/**
	 * 根据【通行证ID】查询【购物车中作品】的数量
	 * @param passportId 【通行证ID】
	 * @return 【购物车中作品】的数量
	 * @throws Exception
	 */
	@Select( value = { "SELECT COUNT(1) FROM bud_edit w WHERE w.DB_ID IN (SELECT td.edt_db_id FROM bud_car td WHERE td.status = 1 AND td.user_db_id = #{passportId, javaType=Integer, jdbcType=INTEGER}) " } )
	int selectTrolleyWorksCountByPassportId(Integer passportId) throws Exception;
}
